import mysql from 'mysql2/promise';
import dotenv from 'dotenv';
import fs from 'fs/promises';
import path from 'path';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

dotenv.config();

async function setupDatabase() {
  let connection;
  
  try {
    // 首先尝试不指定数据库连接
    connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD
    });

    // 创建数据库
    await connection.query(`CREATE DATABASE IF NOT EXISTS ${process.env.DB_NAME}`);
    console.log(`Database ${process.env.DB_NAME} created or already exists`);

    // 使用数据库
    await connection.query(`USE ${process.env.DB_NAME}`);

    // 读取并执行初始化SQL
    const initSql = await fs.readFile(
      path.join(__dirname, '../init.sql'),
      'utf8'
    );

    const statements = initSql
      .split(';')
      .filter(statement => statement.trim());

    for (const statement of statements) {
      if (statement.trim()) {
        await connection.query(statement);
      }
    }

    // 创建新用户并授权
    await connection.query(`
      CREATE USER IF NOT EXISTS '${process.env.DB_USER}'@'localhost' 
      IDENTIFIED BY '${process.env.DB_PASSWORD}'
    `);

    await connection.query(`
      GRANT ALL PRIVILEGES ON ${process.env.DB_NAME}.* 
      TO '${process.env.DB_USER}'@'localhost'
    `);

    await connection.query('FLUSH PRIVILEGES');

    console.log('Database setup completed successfully');
  } catch (error) {
    console.error('Database setup failed:', error);
    throw error;
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

setupDatabase().catch(console.error);